Question: What would you do keeping in mind that the customer uses this search product? How can you use this data to bring out insights to improve the search product?
Definitions of some terms:
Hits - Number of Search Queries
Click-throughs - Number of times visitors clicked on any one of the results in the search listing page
Clicks - Cumulative Number of times visitors clicked on any result in the search listing page
Clicks would always be greater or equal to than clickthroughs
CTR = Clickthrough rate = Click Throughs/Hits
Orders - Orders placed against the search query (Query report) or for the particular product)
You can ignore the initial price and final price from your analysis
import pandas as pd
import plotly.express as px
from pandas_profiling import ProfileReport
import numpy as np
import plotly.graph_objects as go
df = pd.read_excel('Customer Raw data.xlsx', sheet_name=2)
df
c:\users\admin\appdata\local\programs\python\python38\lib\site-packages\openpyxl\worksheet\_reader.py:312: UserWarning: Unknown extension is not supported and will be removed warn(msg)
| query | hits | clicks | clickThroughs | ctr | carts | orders | |
|---|---|---|---|---|---|---|---|
| 0 | pink | 3971 | 330 | 93 | 2.34 | 18 | 0 |
| 1 | Chambray | 612 | 1360 | 447 | 73.04 | 145 | 8 |
| 2 | gingham in green and white | 601 | 0 | 0 | 0.00 | 0 | 0 |
| 3 | white dresses | 486 | 800 | 310 | 63.79 | 73 | 1 |
| 4 | grey | 480 | 89 | 28 | 5.83 | 10 | 0 |
| ... | ... | ... | ... | ... | ... | ... | ... |
| 495 | Pleated knit | 14 | 12 | 6 | 42.86 | 0 | 0 |
| 496 | Contrast | 14 | 27 | 7 | 50.00 | 4 | 0 |
| 497 | 1940s | 14 | 8 | 4 | 28.57 | 4 | 0 |
| 498 | crimson | 14 | 19 | 5 | 35.71 | 8 | 0 |
| 499 | watercolor | 14 | 27 | 9 | 64.29 | 0 | 0 |
500 rows × 7 columns
profile = ProfileReport(df, title="Customer Unbxd Query performance Report")
profile.to_notebook_iframe()
len(df.groupby(['query']))
500
fig = px.bar(df.sort_values(
['hits'],
ascending=False,
)[0:50],
x='query',
color='orders',
y='orders',
color_continuous_scale='Reds')
fig.update_layout(
xaxis_tickangle=90,
title_text=
'Top 50 Queries hits v/s Orders placed (Please Zoom to Have Detailed Analysis)'
)
fig.show()
features = ["hits", "clicks", "clickThroughs", "carts"]
fig = px.scatter_matrix(df.sort_values(
['hits'],
ascending=False,
),
dimensions=features,
color="orders",
hover_data=['query'])
fig.update_traces(diagonal_visible=False, )
fig.update_layout(
title_text=
'Scatter Plot Between Features Query, Hits,Clicks, Click Throughs, Carts and Orders (Please Zoom)'
)
fig.show()
fig = px.parallel_coordinates(
df,
color="orders",
color_continuous_scale=px.colors.diverging.Tealrose,
color_continuous_midpoint=4)
fig.update_layout(title_text='Features Relation Mapping Among ')
fig.show()
fig = px.funnel(df.sort_values(
['ctr'],
ascending=False,
)[0:20],
x='ctr',
y='query',
color='query')
fig.update_layout(
title_text='Query Wise Funnel Cycles for Top 20 CTR Percentage')
fig.show()
Top_ctr1 = df.sort_values(
['ctr'],
ascending=False,
)[0:1]
Top_ctr2 = df.sort_values(
['ctr'],
ascending=False,
)[1:2]
Top_ctr3 = df.sort_values(
['ctr'],
ascending=False,
)[2:3]
Top_ctr4 = df.sort_values(
['ctr'],
ascending=False,
)[3:4]
Top_ctr5 = df.sort_values(
['ctr'],
ascending=False,
)[4:5]
Top_ctr1 = Top_ctr1[[
'clicks', 'hits', 'clickThroughs', 'carts', 'orders', 'ctr', 'query'
]]
Top_ctr2 = Top_ctr2[[
'clicks', 'hits', 'clickThroughs', 'carts', 'orders', 'ctr', 'query'
]]
Top_ctr3 = Top_ctr3[[
'clicks', 'hits', 'clickThroughs', 'carts', 'orders', 'ctr', 'query'
]]
Top_ctr4 = Top_ctr4[[
'clicks', 'hits', 'clickThroughs', 'carts', 'orders', 'ctr', 'query'
]]
Top_ctr5 = Top_ctr5[[
'clicks', 'hits', 'clickThroughs', 'carts', 'orders', 'ctr', 'query'
]]
y = ['clicks', 'hits', 'clickThroughs', 'carts', 'orders']
fig = go.Figure()
fig.add_trace(
go.Funnel(name=Top_ctr1['query'].values[0],
y=y,
x=Top_ctr1.drop(columns=['query', 'ctr']).values[0],
textinfo="value+percent initial"))
fig.add_trace(
go.Funnel(name=Top_ctr2['query'].values[0],
y=y,
x=Top_ctr2.drop(columns=['query', 'ctr']).values[0],
textinfo="value+percent initial"))
fig.add_trace(
go.Funnel(name=Top_ctr3['query'].values[0],
y=y,
x=Top_ctr3.drop(columns=['query', 'ctr']).values[0],
textinfo="value+percent initial"))
fig.add_trace(
go.Funnel(name=Top_ctr4['query'].values[0],
y=y,
x=Top_ctr4.drop(columns=['query', 'ctr']).values[0],
textinfo="value+percent initial"))
fig.add_trace(
go.Funnel(name=Top_ctr5['query'].values[0],
y=y,
x=Top_ctr5.drop(columns=['query', 'ctr']).values[0],
textinfo="value+percent initial"))
fig.update_layout(title_text='Complete Funnel Cycles for Top 5 CTR values for Queries')
fig.show()
df_0 = pd.read_excel('Customer Raw data.xlsx', sheet_name=0)
df_0
c:\users\admin\appdata\local\programs\python\python38\lib\site-packages\openpyxl\worksheet\_reader.py:312: UserWarning: Unknown extension is not supported and will be removed
| PID | Product Name | Category | Final Price | Initial Price | Color | Primary Color | Fabric | |
|---|---|---|---|---|---|---|---|---|
| 0 | CL0036386 | Ikat stripe print cotton knit dress | Dress | 84.95 | 33.95 | Blue/navy/white | Blue | Cotton Spandex |
| 1 | CL0030567 | Feminine pleated knit dress | Dress | 59.95 | 41.95 | Navy blue | Navy blue | Cotton Spandex |
| 2 | CL0032320 | Floral embellished chambray denim maxi dress | Dress | 96.95 | 67.95 | Deep indigo | Deep indigo | Cotton |
| 3 | CL0036700 | Dot print cap sleeve shirt | Top | 49.95 | 19.95 | White/navy | White | Cotton |
| 4 | CL0036365 | Layered asymmetric hem hacci knit top | Top | 69.95 | 27.95 | Blue | Blue | Polyester |
| ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 711 | CL0031288 | Ruffle front poplin dress | Dress | 69.95 | 52.95 | Spring green | Spring green | Cotton |
| 712 | CL0032199 | Beaded cotton poplin sheath dress | Dress | 86.95 | 65.95 | Spring green | Spring green | Cotton |
| 713 | CL0031267 | Embellished stripe waist poplin dress | Dress | 76.95 | 57.95 | Spring green multi | Spring green multi | Cotton |
| 714 | CL0031287 | Sash waist poplin skirt | Skirt | 59.95 | 44.95 | Spring green | Spring green | Cotton |
| 715 | CL0031947 | Colorblock retro poplin frock | Dress | 59.95 | 44.95 | Black/spring green | Black | Cotton |
716 rows × 8 columns
df_1 = pd.read_excel('Customer Raw data.xlsx', sheet_name=1)
df_1
c:\users\admin\appdata\local\programs\python\python38\lib\site-packages\openpyxl\worksheet\_reader.py:312: UserWarning: Unknown extension is not supported and will be removed
| pId | pName | clicks | carts | orders | |
|---|---|---|---|---|---|
| 0 | CL0034516 | Chevron stripe colorblock maxi dress | 29144 | 2181 | 204 |
| 1 | CL0037085 | Chevron stripe colorblock maxi dress | 25892 | 237 | 70 |
| 2 | CL0036758 | Bird print pleated cotton knit dress | 16343 | 340 | 97 |
| 3 | CL0032310 | Cotton knit fit and flare dress | 16234 | 350 | 99 |
| 4 | CL0037110 | Pleat front chambray denim maxi dress | 13317 | 229 | 56 |
| ... | ... | ... | ... | ... | ... |
| 2496 | CL0036376 | Name N/A | 0 | 0 | 1 |
| 2497 | CL0035627 | Heart print cotton A-line dress | 0 | 0 | 1 |
| 2498 | GC0000202 | Name N/A | 0 | 0 | 5 |
| 2499 | GC0000204 | Name N/A | 0 | 0 | 7 |
| 2500 | GC0000205 | Name N/A | 0 | 0 | 11 |
2501 rows × 5 columns
df_1.rename({'pId': 'PID'}, axis='columns', inplace=True)
df_1 = pd.merge(df_0, df_1, on="PID").drop(columns='pName')
df_1
| PID | Product Name | Category | Final Price | Initial Price | Color | Primary Color | Fabric | clicks | carts | orders | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | CL0036386 | Ikat stripe print cotton knit dress | Dress | 84.95 | 33.95 | Blue/navy/white | Blue | Cotton Spandex | 1846 | 43 | 15 |
| 1 | CL0030567 | Feminine pleated knit dress | Dress | 59.95 | 41.95 | Navy blue | Navy blue | Cotton Spandex | 6882 | 242 | 56 |
| 2 | CL0032320 | Floral embellished chambray denim maxi dress | Dress | 96.95 | 67.95 | Deep indigo | Deep indigo | Cotton | 10680 | 72 | 27 |
| 3 | CL0036700 | Dot print cap sleeve shirt | Top | 49.95 | 19.95 | White/navy | White | Cotton | 2051 | 59 | 24 |
| 4 | CL0036365 | Layered asymmetric hem hacci knit top | Top | 69.95 | 27.95 | Blue | Blue | Polyester | 4630 | 123 | 50 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 711 | CL0031288 | Ruffle front poplin dress | Dress | 69.95 | 52.95 | Spring green | Spring green | Cotton | 62 | 8 | 0 |
| 712 | CL0032199 | Beaded cotton poplin sheath dress | Dress | 86.95 | 65.95 | Spring green | Spring green | Cotton | 380 | 4 | 0 |
| 713 | CL0031267 | Embellished stripe waist poplin dress | Dress | 76.95 | 57.95 | Spring green multi | Spring green multi | Cotton | 175 | 16 | 2 |
| 714 | CL0031287 | Sash waist poplin skirt | Skirt | 59.95 | 44.95 | Spring green | Spring green | Cotton | 108 | 15 | 0 |
| 715 | CL0031947 | Colorblock retro poplin frock | Dress | 59.95 | 44.95 | Black/spring green | Black | Cotton | 2774 | 102 | 24 |
716 rows × 11 columns
df_1['Category'].value_counts()
Dress 428 Top 147 Skirt 72 Pant 49 Jumpsuit 11 Jacket 9 Name: Category, dtype: int64
df_1.sort_values(
['orders'],
ascending=False,
)[0:50]
| PID | Product Name | Category | Final Price | Initial Price | Color | Primary Color | Fabric | clicks | carts | orders | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 669 | CL0036758 | Seagull print pleated cotton knit dress | Dress | 89.95 | 67.95 | Light blue/white/black | Light blue | Cotton Spandex | 16343 | 340 | 97 |
| 11 | CL0030370 | Pleat waist poplin shirtdress | Dress | 59.95 | 41.95 | Deep navy | Deep navy | Cotton | 9571 | 367 | 89 |
| 114 | CL0032653 | Cotton poplin A-line shirtdress | Dress | 59.95 | 41.95 | Black | Black | Cotton | 8287 | 247 | 82 |
| 325 | CL0036806 | Retro chambray shirtdress | Dress | 79.95 | 55.95 | Indigo | Indigo | Cotton | 8407 | 227 | 71 |
| 653 | CL0037085 | Chevron stripe colorblock maxi dress | Dress | 99.95 | 79.95 | Black/white/eggplant | Black | Cotton | 25892 | 237 | 70 |
| 698 | CL0037156 | Chevron stripe colorblock maxi dress | Dress | 99.95 | 79.95 | Spring green/white/deep navy | Spring green | Cotton | 12364 | 172 | 68 |
| 670 | CL0037110 | Pleat front chambray denim maxi dress | Dress | 69.95 | 52.95 | Indigo | Indigo | Cotton | 13317 | 229 | 56 |
| 1 | CL0030567 | Feminine pleated knit dress | Dress | 59.95 | 41.95 | Navy blue | Navy blue | Cotton Spandex | 6882 | 242 | 56 |
| 13 | CL0033768 | Tie neck poplin dress | Dress | 59.95 | 44.95 | Black | Black | Cotton | 7175 | 230 | 51 |
| 418 | CL0034517 | Chevron stripe colorblock maxi dress | Dress | 99.95 | 79.95 | Indigo/deep navy/khaki | Indigo | Cotton | 6229 | 228 | 50 |
| 4 | CL0036365 | Layered asymmetric hem hacci knit top | Top | 69.95 | 27.95 | Blue | Blue | Polyester | 4630 | 123 | 50 |
| 686 | CL0037096 | Elastic waist gingham maxi dress | Dress | 69.95 | 52.95 | Black/white | Black | Cotton | 6848 | 103 | 42 |
| 15 | CL0036693 | Graphic flying bird print cotton dress | Dress | 89.95 | 35.95 | Black/off-white | Black | Cotton | 2932 | 72 | 40 |
| 7 | CL0035516 | Floral vine print cotton dress | Dress | 69.95 | 48.95 | Off-white/teal multi | Off-white | Cotton Spandex | 3390 | 85 | 38 |
| 5 | CL0035569 | Sash waist chambray dress | Dress | 69.95 | 48.95 | Indigo | Indigo | Cotton | 5338 | 128 | 37 |
| 503 | CL0033318 | Kayla dress | Dress | 59.95 | 59.95 | Deep navy | Deep navy | Cotton | 5174 | 181 | 37 |
| 675 | CL0037049 | Pinstripe cotton knit dress | Dress | 59.95 | 44.95 | Black/gray/white | Black | Cotton Spandex | 3030 | 62 | 36 |
| 191 | CL0032666 | Embellished floral yoke poplin dress | Dress | 96.95 | 67.95 | Black multi | Black multi | Cotton | 2904 | 88 | 34 |
| 680 | CL0037039 | Floral stripe print belted dress | Dress | 59.95 | 44.95 | Red/cream | Red | Cotton | 5015 | 134 | 33 |
| 433 | CL0037051 | Bow tie back woven ikat check dress | Dress | 69.95 | 52.95 | Red/navy/white | Red | Cotton | 8373 | 67 | 33 |
| 430 | CL0027313 | Chateau cotton poplin dress | Dress | 59.95 | 41.95 | Poppy red | Poppy red | Cotton | 3645 | 79 | 31 |
| 46 | CL0036797 | Vintage denim chambray dress | Dress | 64.95 | 45.95 | Indigo | Indigo | Cotton | 4264 | 124 | 29 |
| 642 | CL0036972 | Floral dot print high-low hem dress | Dress | 99.95 | 69.95 | Deep navy/yellow/white | Deep navy | Cotton | 6335 | 77 | 29 |
| 610 | CL0037102 | Chevron stripe colorblock maxi dress | Dress | 99.95 | 79.95 | Poppy red/white/deep navy | Poppy red | Cotton | 857 | 46 | 29 |
| 645 | CL0036902 | Graphic floral print colorblock dress | Dress | 89.95 | 62.95 | Black/cream/navy multi | Black | Cotton Spandex | 3999 | 58 | 28 |
| 182 | CL0033124 | Chambray denim A-line shirtdress | Dress | 69.95 | 48.95 | Deep indigo | Deep indigo | Cotton | 2333 | 142 | 28 |
| 8 | CL0036337 | Circle print diamond knit dress | Dress | 59.95 | 41.95 | White/green/black | White | Cotton Spandex | 2818 | 66 | 28 |
| 540 | CL0032313 | Cotton knit fit and flare dress | Dress | 69.95 | 34.95 | Deep navy | Deep navy | Cotton Spandex | 1426 | 91 | 27 |
| 217 | CL0036485 | Brooklyn dress | Dress | 59.95 | 29.95 | Brown | Brown | Cotton | 3204 | 171 | 27 |
| 2 | CL0032320 | Floral embellished chambray denim maxi dress | Dress | 96.95 | 67.95 | Deep indigo | Deep indigo | Cotton | 10680 | 72 | 27 |
| 25 | CL0029095 | Maritime tee | Top | 49.95 | 34.95 | Bottle green | Bottle green | Cotton Spandex | 958 | 19 | 26 |
| 655 | CL0037082 | Pleated surplice cotton knit wrap dress | Dress | 79.95 | 59.95 | Purple | Purple | Cotton Spandex | 3879 | 82 | 26 |
| 190 | CL0036796 | Chevron stripe colorblock maxi dress | Dress | 99.95 | 79.95 | White/black | White | Cotton | 8280 | 71 | 26 |
| 66 | CL0032757 | Dahlia crinoline | Skirt | 79.95 | 55.95 | Black | Black | Polyester | 3806 | 138 | 26 |
| 538 | CL0036617 | Seamed fit-and flare sateen dress | Dress | 79.95 | 31.95 | Mayo teal | Mayo teal | Cotton Spandex | 2169 | 72 | 26 |
| 44 | CL0036798 | Citron floral print dress | Dress | 64.95 | 45.95 | Citron multi | Citron multi | Cotton Spandex | 2559 | 97 | 26 |
| 665 | CL0026054 | Shawl collar poplin shirtdress | Dress | 59.95 | 41.95 | Poppy red | Poppy red | Cotton | 3487 | 111 | 26 |
| 120 | CL0035883 | Contrast trim poplin dress | Dress | 64.95 | 45.95 | Deep navy/white | Deep navy | Cotton | 2977 | 110 | 25 |
| 48 | CL0035581 | Skater style cotton knit dress | Dress | 64.95 | 45.95 | Navy | Navy | Cotton Spandex | 2720 | 44 | 25 |
| 69 | CL0035605 | Cowl neck birdsong dress | Dress | 86.95 | 60.95 | Navy blue | Navy blue | Cotton Spandex | 2179 | 56 | 25 |
| 715 | CL0031947 | Colorblock retro poplin frock | Dress | 59.95 | 44.95 | Black/spring green | Black | Cotton | 2774 | 102 | 24 |
| 91 | CL0036809 | Tie-neck cotton knit dress | Dress | 64.95 | 48.95 | Bottle green | Bottle green | Cotton Spandex | 3693 | 82 | 24 |
| 3 | CL0036700 | Dot print cap sleeve shirt | Top | 49.95 | 19.95 | White/navy | White | Cotton | 2051 | 59 | 24 |
| 23 | CL0030235 | Cotton knit curved waist dress | Dress | 59.95 | 41.95 | Navy blue | Navy blue | Cotton Spandex | 1882 | 91 | 23 |
| 111 | CL0036640 | Polka dot voile print keyhole dress | Dress | 89.95 | 44.95 | Royal blue/off-white | Royal blue | Cotton | 2323 | 83 | 23 |
| 20 | CL0034047 | Aurora dress | Dress | 99.95 | 99.95 | Black | Black | Cotton Nylon | 2352 | 31 | 23 |
| 398 | CL0037069 | Bow tie cutout back cotton knit dress | Dress | 74.95 | 56.95 | Purple | Purple | Cotton Spandex | 8231 | 51 | 23 |
| 105 | CL0036393 | Piped trim polka dot print dress | Dress | 119.95 | 47.95 | Off-white/navy | Off-white | Cotton | 3096 | 64 | 22 |
| 410 | CL0036929 | Contrast collar woven check shirtdress | Dress | 74.95 | 52.95 | Beige/navy multi | Beige | Cotton | 2199 | 44 | 21 |
| 127 | CL0036747 | Graphic mixed fruit print dress | Dress | 109.95 | 43.95 | Off-white/red/navy | Off-white | Cotton | 2774 | 43 | 21 |
profile = ProfileReport(
df_1, title="Product Description and Performance Data Report")
profile.to_notebook_iframe()
df_1.groupby('Category').sum().drop(columns=['Final Price', 'Initial Price'])
| clicks | carts | orders | |
|---|---|---|---|
| Category | |||
| Dress | 718466 | 14922 | 3991 |
| Jacket | 5768 | 158 | 42 |
| Jumpsuit | 11006 | 148 | 31 |
| Pant | 23170 | 449 | 148 |
| Skirt | 55518 | 1590 | 396 |
| Top | 82008 | 2154 | 701 |
fig = px.bar(df_1.groupby('Category').sum().drop(columns=['Final Price','Initial Price']).T,) \
#x='orders',#df_1.groupby('Category').sum().drop(columns=['Final Price','Initial Price']).index,#color='orders',\
#y=['orders','clicks','carts'],color_continuous_scale='Reds', barmode="group",
#facet_row=df_1.groupby('Category').sum().drop(columns=['Final Price','Initial Price']).columns, )#facet_col="day",)
fig.update_layout(
xaxis_tickangle=90,
title_text='Clicks,Carts and Orders for Different \
Categories (Please Zoom to Have Detailed Analysis)',
yaxis=dict(
title='Total Count',
titlefont_size=16,
tickfont_size=14,
),
xaxis=dict(
title='Users Preference for Category in Clicks,Carts and Orders',
titlefont_size=16,
tickfont_size=14,
))
fig.show()
fig = px.bar(
df_1.groupby('Fabric').sum().drop(
columns=['Final Price', 'Initial Price']).T, )
fig.update_layout(
xaxis_tickangle=90,
title_text=
'Clicks,Carts and Orders for Different Fabrics (Please Zoom to Have Detailed Analysis)',
yaxis=dict(
title='Total Count',
titlefont_size=16,
tickfont_size=14,
),
xaxis=dict(
title='Users Preference for Fabric in Clicks,Carts and Orders',
titlefont_size=16,
tickfont_size=14,
))
fig.show()
df_pie = df_1.copy()
df_pie.loc[df_pie['orders'] < 25, 'Primary Color'] = 'Other Colors'
fig = px.pie(df_pie, values='orders', names='Primary Color')
fig.update_layout(
xaxis_tickangle=90,
title_text='Users Preferance of orders Based on Color of orders',
)
fig.show()
conversions to the customers can be provided by having more Focus on Black Color and Indigo Color Dress as more users are Intrested in those Color.
Apart from this People Love more Cotton and Cotton spandix Fabric as maximum orders.